﻿
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Xml;
using System.Diagnostics;
using System.Collections;

namespace DataAccess.ODBC
{
/// 
/// Odbc对象操作
/// 
    [DebuggerStepThroughAttribute]
    public sealed class OdbcHelper
    {
        /// 
        /// 构造器
        /// 
        private OdbcHelper() { }
      /// 創建查詢結果
        /// </summary>
        /// <param name="sTblName">表名</param>
        /// <param name="sFields">要显示的列的名称</param>
        /// <param name="strOrder">用来排序的語句 order by</param>
        /// <param name="iPageSize">页显示的数据条数</param>
        /// <param name="iPageIndex">页码</param>
        /// <param name="sWhere">查询条件</param>
        /// <returns></returns>
        public static DataTable getResult(string sTblName, string sFields, string strOrder, int iPageSize, int iPageIndex, string sWhere)
        {

            #region SQL   
            string sConn = ODBCDSN.SqlConn(System.Web.HttpContext.Current.Session["EnterPrise"].ToString());

            System.Data.SqlClient.SqlParameter[] sqlParam = new System.Data.SqlClient.SqlParameter[7];
            sqlParam[0] = new System.Data.SqlClient.SqlParameter("@tblName", sTblName);

            sqlParam[1] = new System.Data.SqlClient.SqlParameter("@strGetFields", sFields);

            //@strGetFields           varchar(1000),                     --              例如:set @strGetFields = 'A.ID,A.条码,B.仓库名称'
            sqlParam[2] = new System.Data.SqlClient.SqlParameter("@strOrder", strOrder);

            sqlParam[3] = new System.Data.SqlClient.SqlParameter("@pageSize", SqlDbType.Int);
            sqlParam[3].Value = iPageSize;
            //@pageSize               int,                               --              例如:set @pageSize = '10'
            sqlParam[4] = new System.Data.SqlClient.SqlParameter("@pageIndex", SqlDbType.Int);
            sqlParam[4].Value = iPageIndex;
            //@pageIndex              int,                               --                         例如:set @pageIndex = '1'
            sqlParam[5] = new System.Data.SqlClient.SqlParameter("@doCount", SqlDbType.Bit);
            sqlParam[5].Value = 0;
            //@doCount                bit,                               --返回记录总数，非则返回
            sqlParam[6] = new System.Data.SqlClient.SqlParameter("@strWhere", sWhere);
            return SQL.SqlHelper.ExecuteDataset(sConn, CommandType.StoredProcedure, "SP_GetDataPage", sqlParam).Tables[0];
            
            #endregion

            #region ODBC

            //OdbcParameter[] param = new OdbcParameter[7];
            //param[0] = new OdbcParameter("@tblName", sTblName);
            ////            @tblName                varchar(255),                      --                          例如:set @tblName = 'table1 A,table2 B'
            //param[1] = new OdbcParameter("@strGetFields", sFields);

            ////@strGetFields           varchar(1000),                     --              例如:set @strGetFields = 'A.ID,A.条码,B.仓库名称'
            //param[2] = new OdbcParameter("@strOrder", strOrder);

            //param[3] = new OdbcParameter("@pageSize", OdbcType.Int);
            //param[3].Value = iPageSize;
            ////@pageSize               int,                               --              例如:set @pageSize = '10'
            //param[4] = new OdbcParameter("@pageIndex", OdbcType.Int);
            //param[4].Value = iPageIndex;
            ////@pageIndex              int,                               --                         例如:set @pageIndex = '1'
            //param[5] = new OdbcParameter("@doCount", OdbcType.Bit);
            //param[5].Value = 0;
            ////@doCount                bit,                               --返回记录总数，非则返回
            //param[6] = new OdbcParameter("@strWhere", sWhere);
            ////@strWhere               varchar(1500)                      --                      例如:set @strWhere = 'and A.仓库ID = B.ID'

            //DataTable tblResult = new DataTable();
            //string[] cols = sFields.Split(',');


            //for (int i = 0; i < cols.Length; i++)
            //{
            //    // select col as 'aaa'
            //    cols[i] = cols[i].Replace(" as", "").Replace("'", "").Trim();
            //    tblResult.Columns.Add(cols[i]);
            //}

            //string sCon = ODBCDSN.WwsCustDb;
            //OdbcConnection con = new OdbcConnection(sCon);
            //OdbcCommand cmd = new OdbcCommand("{ CALL SP_GetDataPage (?,?,?,?,?,?,?)}", con);
            //cmd.CommandType = CommandType.StoredProcedure;
            //for (int j = 0; j < param.Length; j++) cmd.Parameters.Add(param[j]);
            //con.Open();
            //OdbcDataReader reader = cmd.ExecuteReader();
            //while (reader.Read())
            //{

            //    DataRow row = tblResult.NewRow();
            //    for (int i = 0; i < cols.Length; i++)
            //    {

            //        row[cols[i].Trim()] = reader[cols[i].Trim()];
            //    }
            //    tblResult.Rows.Add(row);
            //}
            //reader.Close();
            //reader.Dispose();
            //con.Close();
            //con.Dispose();
            //return tblResult; 
            #endregion
        }

        #region private Parameters
        /// 
        /// 为OdbcCommand添加参数
        /// 
        /// OdbcCommand对象
        /// OdbcParameter[]对象
        private static void AttachParameters(OdbcCommand command, OdbcParameter[] commandParameters)
        {
            foreach (OdbcParameter p in commandParameters)
            {
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; }
                command.Parameters.Add(p);
            }
        }



  

        /// 
        /// 为OdbcParameter[]添加值
        /// 
        /// OdbcParameter[]对象
        /// object[]对象
        private static void AssignParameterValues(OdbcParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null)) return;
            if (commandParameters.Length != parameterValues.Length) throw new ArgumentException("Parameter count does not match Parameter Value count.");
            for (int i = 0, j = commandParameters.Length; i < j; i++) commandParameters[i].Value = parameterValues[i];
        }
        /// 
        /// 这种方法打开,并指派一个connection, transaction, command type and parameters
        /// 
        /// OdbcCommand对象
        /// OdbcConnection对象
        /// OdbcTransaction对象
        /// CommandType对象
        /// Sql字符串
        /// OdbcParameter[]对象
        private static void PrepareCommand(OdbcCommand command, OdbcConnection connection, OdbcTransaction transaction, CommandType commandType, string commandText, OdbcParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null) command.Transaction = transaction;
            command.CommandType = commandType;
            if (commandParameters != null) AttachParameters(command, commandParameters);
            return;
        }
        #endregion

        #region MakeParam
        /// 
        /// object转为字符串
        /// 
        /// object对象
        /// object转为字符串
        public static string CheckNull(object obj) { return (string)obj; }
        /// 
        /// 数据库字段值是否为空
        /// 
        /// DBNull对象
        /// null字符串
        public static string CheckNull(DBNull obj) { return null; }

        /// 
        /// 判断字符串是否为空，并将字符串转为object对象
        /// 
        /// 字符串
        /// object对象也可能为DBNull
        public static object CheckForNullString(string text)
        {
            if (text == null || text.Trim().Length == 0) return System.DBNull.Value; else return text;
        }
        /// 
        /// 组成一个OdbcParameter参数
        /// 
        /// 参数名
        /// 参数值
        /// 返回一个OdbcParameter参数
        public static OdbcParameter MakeInParam(string ParamName, object Value) { return new OdbcParameter(ParamName, Value); }
        /// 
        /// 组成一个OdbcParameter参数
        /// 
        /// 参数名
        /// 参数类型
        /// 参数类型大小
        /// 参数值
        /// 返回一个OdbcParameter参数
        public static OdbcParameter MakeInParam(string ParamName, OdbcType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); }
        /// 
        /// 輸出一个OdbcParameter参数
        /// 
        /// 参数名
        /// 参数类型
        /// 参数类型大小
        /// 返回一个OdbcParameter参数
        public static OdbcParameter MakeOutParam(string ParamName, OdbcType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); }
        /// 
        /// 构造一个OdbcParameter参数值
        /// 
        /// 参数名
        /// 参数类型
        /// 参数类型大小
        /// 輸入/輸出
        /// 参数值
        /// 返回一个OdbcParameter参数
        public static OdbcParameter MakeParam(string ParamName, OdbcType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            OdbcParameter param;
            if (Size > 0) param = new OdbcParameter(ParamName, DbType, Size); else param = new OdbcParameter(ParamName, DbType);
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value;
            return param;
        }
        #endregion

        #region ExecuteNonQuery
        /// 
        /// 执行一个Odbc，无记录集返回
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// 引响多少行
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connectionString, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，无记录集返回
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 引响多少行
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcConnection cn = new OdbcConnection(connectionString);
            cn.Open();
            return ExecuteNonQuery(cn, commandType, commandText, commandParameters);

        }
        /// 
        /// 执行一个Odbc，无记录集返回
        /// 
        /// OdbcConnection对象
        /// CommandType
        /// Sql
        /// 引响多少行
        public static int ExecuteNonQuery(OdbcConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connection, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，无记录集返回
        /// 
        /// OdbcConnection对象
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 引响多少行
        public static int ExecuteNonQuery(OdbcConnection connection, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, connection, (OdbcTransaction)null, commandType, commandText, commandParameters);
            int retval = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            connection.Close();
            return retval;
        }
        /// 
        /// 事务执行一个Odbc，无记录集返回
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// 引响多少行
        public static int ExecuteNonQuery(OdbcTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(transaction, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 事务执行一个Odbc，无记录集返回
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 引响多少行
        public static int ExecuteNonQuery(OdbcTransaction transaction, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
            int retval = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            //transaction.Connection.Close();
            //transaction.Dispose();
            return retval;
        }
        #endregion

        #region ExecuteDataset
        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // If we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                OdbcParameter[] commandParameters = OdbcHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes an array of OdbcParameters
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// 
        /// 执行一个Odbc，返回DataSet
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// 返回DataSet
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回DataSet
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回DataSet
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            using (OdbcConnection cn = new OdbcConnection(connectionString))
            {
                cn.Open();
                return ExecuteDataset(cn, commandType, commandText, commandParameters);
            }
        }
        /// 
        /// 执行一个Odbc，返回DataSet
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// 返回DataSet
        public static DataSet ExecuteDataset(OdbcConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回DataSet
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回DataSet
        public static DataSet ExecuteDataset(OdbcConnection connection, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, connection, (OdbcTransaction)null, commandType, commandText, commandParameters);
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cmd.Parameters.Clear();
            connection.Close();
            return ds;
        }
        /// 
        /// 执行一个Odbc，返回DataSet
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// 返回DataSet
        public static DataSet ExecuteDataset(OdbcTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataset(transaction, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回DataSet
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回DataSet
        public static DataSet ExecuteDataset(OdbcTransaction transaction, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cmd.Parameters.Clear();
            //transaction.Connection.Close();
            //transaction.Dispose();
            return ds;
        }
        #endregion

        #region ExecuteDataTable
        /// 
        /// 执行一个Odbc，返回DataTable
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// 返回DataTable
        public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataTable(connectionString, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回DataTable
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回DataTable
        public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            using (OdbcConnection cn = new OdbcConnection(connectionString))
            {
                cn.Open();
                return ExecuteDataTable(cn, commandType, commandText, commandParameters);
            }
        }
        /// 
        /// 执行一个Odbc，返回DataTable
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// 返回DataTable
        public static DataTable ExecuteDataTable(OdbcConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataTable(connection, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回DataTable
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回DataTable
        public static DataTable ExecuteDataTable(OdbcConnection connection, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, connection, (OdbcTransaction)null, commandType, commandText, commandParameters);
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cmd.Parameters.Clear();
            connection.Close();
            return dt;
        }
        /// 
        /// 执行一个Odbc，返回DataTable
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// 返回DataTable
        public static DataTable ExecuteDataTable(OdbcTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataTable(transaction, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回DataTable
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回DataTable
        public static DataTable ExecuteDataTable(OdbcTransaction transaction, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cmd.Parameters.Clear();
            //transaction.Connection.Close();
            //transaction.Dispose();
            return dt;
        }
        #endregion

        #region ExecuteReader
        /// 
        /// Odbc连接身份
        /// 
        private enum OdbcConnectionOwnership
        {
            /// 
            /// 内部的
            /// 
            Internal,
            /// 
            /// 外部的
            /// 
            External
        }
        /// 
        /// 执行一个Odbc，返回OdbcDataReader
        /// 
        /// OdbcConnection
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// Odbc连接身份
        /// 返回OdbcDataReader
        private static OdbcDataReader ExecuteReader(OdbcConnection connection, OdbcTransaction transaction, CommandType commandType, string commandText, OdbcParameter[] commandParameters, OdbcConnectionOwnership connectionOwnership)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
            OdbcDataReader dr;
            if (connectionOwnership == OdbcConnectionOwnership.External) dr = cmd.ExecuteReader(); else dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return dr;
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  OdbcDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>A OdbcDataReader containing the resultset generated by the command</returns>
        public static OdbcDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // If we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                OdbcParameter[] commandParameters = OdbcHelperParameterCache.GetSpParameterSet(connectionString, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        }
        /// 
        /// 执行一个Odbc，返回OdbcDataReader
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// 返回OdbcDataReader
        public static OdbcDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(connectionString, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回OdbcDataReader
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回OdbcDataReader
        public static OdbcDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcConnection cn = new OdbcConnection(connectionString);
            cn.Open();

            try
            {
                return ExecuteReader(cn, null, commandType, commandText, commandParameters, OdbcConnectionOwnership.Internal);
            }
            catch
            {
                cn.Close();
                throw;
            }
        }
        /// 
        /// 执行一个Odbc，返回OdbcDataReader
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// 返回OdbcDataReader
        public static OdbcDataReader ExecuteReader(OdbcConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteReader(connection, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回OdbcDataReader
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回OdbcDataReader
        public static OdbcDataReader ExecuteReader(OdbcConnection connection, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            return ExecuteReader(connection, (OdbcTransaction)null, commandType, commandText, commandParameters, OdbcConnectionOwnership.External);
        }
        /// 
        /// 执行一个Odbc，返回OdbcDataReader
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// 返回OdbcDataReader
        public static OdbcDataReader ExecuteReader(OdbcTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteReader(transaction, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回OdbcDataReader
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回OdbcDataReader
        public static OdbcDataReader ExecuteReader(OdbcTransaction transaction, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, OdbcConnectionOwnership.External);
        }

        public static OdbcDataReader ExecuteReader(string connectionString, string spName, params OdbcParameter[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, parameterValues);
        }

        //public static OdbcDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        //{
        //    if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        //    OdbcConnection connection = null;
        //    try
        //    {
        //        connection = new OdbcConnection(connectionString);
        //        connection.Open();

        //        // Call the private overload that takes an internally owned connection in place of the connection string
        //        return ExecuteReader(connection, null, commandType, commandText, commandParameters, OdbcConnectionOwnership.Internal);
        //    }
        //    catch
        //    {
        //        // If we fail to return the SqlDatReader, we need to close the connection ourselves
        //        if (connection != null) connection.Close();
        //        throw;
        //    }

        //}

        #endregion

        #region DataAdapter
        /// 
        /// 执行一个SQL，返回OdbcDataAdapter
        /// 
        /// 连接字符串
        /// CommandType
        /// SQL
        /// 返回OdbcDataAdapter
        public static OdbcDataAdapter DataAdapter(string connectionString, CommandType commandType, string commandText)
        {
            return DataAdapter(connectionString, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个SQL，返回OdbcDataAdapter
        /// 
        /// 连接字符串
        /// CommandType
        /// SQL
        /// OdbcParameter[]参数集
        /// 返回OdbcDataAdapter
        public static OdbcDataAdapter DataAdapter(string connectionString, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcDataAdapter _da = new OdbcDataAdapter(commandText, connectionString);
            _da.SelectCommand.CommandType = commandType;
            if (commandParameters != null) AttachParameters(_da.SelectCommand, commandParameters);
            return _da;
        }
        #endregion

        #region ExecuteScalar
        /// 
        /// 执行一个Odbc，返回第一行的第一列
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// 返回第一行的第一列
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connectionString, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回第一行的第一列
        /// 
        /// 连接字符串
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回第一行的第一列
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcConnection cn = new OdbcConnection(connectionString);

            cn.Open();
            return ExecuteScalar(cn, commandType, commandText, commandParameters);

        }
        /// 
        /// 执行一个Odbc，返回第一行的第一列
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// 返回第一行的第一列
        public static object ExecuteScalar(OdbcConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connection, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回第一行的第一列
        /// 
        /// OdbcConnection
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回第一行的第一列
        public static object ExecuteScalar(OdbcConnection connection, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, connection, (OdbcTransaction)null, commandType, commandText, commandParameters);
            object retval = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            connection.Close();
            return retval;
        }
        /// 
        /// 执行一个Odbc，返回第一行的第一列
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// 返回第一行的第一列
        public static object ExecuteScalar(OdbcTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteScalar(transaction, commandType, commandText, (OdbcParameter[])null);
        }
        /// 
        /// 执行一个Odbc，返回第一行的第一列
        /// 
        /// OdbcTransaction
        /// CommandType
        /// Sql
        /// OdbcParameter[]参数集
        /// 返回第一行的第一列
        public static object ExecuteScalar(OdbcTransaction transaction, CommandType commandType, string commandText, params OdbcParameter[] commandParameters)
        {
            OdbcCommand cmd = new OdbcCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
            object retval = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            //transaction.Connection.Close();
            //transaction.Dispose();
            return retval;
        }
        #endregion
    }


/// <summary>
/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
/// </summary>
public sealed class OdbcHelperParameterCache
{
    #region private methods, variables, and constructors

    //Since this class provides only static methods, make the default constructor private to prevent 
    //instances from being created with "new SqlHelperParameterCache()"
    private OdbcHelperParameterCache() { }

    private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

    /// <summary>
    /// Resolve at run time the appropriate set of OdbcParameters for a stored procedure
    /// </summary>
    /// <param name="connection">A valid OdbcConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
    /// <returns>The parameter array discovered.</returns>
    private static OdbcParameter[] DiscoverSpParameterSet(OdbcConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        OdbcCommand cmd = new OdbcCommand(spName, connection);
        cmd.CommandType = CommandType.StoredProcedure;

        connection.Open();
        OdbcCommandBuilder.DeriveParameters(cmd);
        connection.Close();

        if (!includeReturnValueParameter)
        {
            cmd.Parameters.RemoveAt(0);
        }

        OdbcParameter[] discoveredParameters = new OdbcParameter[cmd.Parameters.Count];

        cmd.Parameters.CopyTo(discoveredParameters, 0);

        // Init the parameters with a DBNull value
        foreach (OdbcParameter discoveredParameter in discoveredParameters)
        {
            discoveredParameter.Value = DBNull.Value;
        }
        return discoveredParameters;
    }

    /// <summary>
    /// Deep copy of cached OdbcParameter array
    /// </summary>
    /// <param name="originalParameters"></param>
    /// <returns></returns>
    private static OdbcParameter[] CloneParameters(OdbcParameter[] originalParameters)
    {
        OdbcParameter[] clonedParameters = new OdbcParameter[originalParameters.Length];

        for (int i = 0, j = originalParameters.Length; i < j; i++)
        {
            clonedParameters[i] = (OdbcParameter)((ICloneable)originalParameters[i]).Clone();
        }

        return clonedParameters;
    }

    #endregion private methods, variables, and constructors

    #region caching functions

    /// <summary>
    /// Add parameter array to the cache
    /// </summary>
    /// <param name="connectionString">A valid connection string for a OdbcConnection</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters to be cached</param>
    public static void CacheParameterSet(string connectionString, string commandText, params OdbcParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

        string hashKey = connectionString + ":" + commandText;

        paramCache[hashKey] = commandParameters;
    }

    /// <summary>
    /// Retrieve a parameter array from the cache
    /// </summary>
    /// <param name="connectionString">A valid connection string for a OdbcConnection</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <returns>An array of SqlParamters</returns>
    public static OdbcParameter[] GetCachedParameterSet(string connectionString, string commandText)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

        string hashKey = connectionString + ":" + commandText;

        OdbcParameter[] cachedParameters = paramCache[hashKey] as OdbcParameter[];
        if (cachedParameters == null)
        {
            return null;
        }
        else
        {
            return CloneParameters(cachedParameters);
        }
    }

    #endregion caching functions

    #region Parameter Discovery Functions

    /// <summary>
    /// Retrieves the set of OdbcParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a OdbcConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <returns>An array of OdbcParameters</returns>
    public static OdbcParameter[] GetSpParameterSet(string connectionString, string spName)
    {
        return GetSpParameterSet(connectionString, spName, false);
    }

    /// <summary>
    /// Retrieves the set of OdbcParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a OdbcConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
    /// <returns>An array of OdbcParameters</returns>
    public static OdbcParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        using (OdbcConnection connection = new OdbcConnection(connectionString))
        {
            return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
        }
    }

    /// <summary>
    /// Retrieves the set of OdbcParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connection">A valid OdbcConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <returns>An array of OdbcParameters</returns>
    internal static OdbcParameter[] GetSpParameterSet(OdbcConnection connection, string spName)
    {
        return GetSpParameterSet(connection, spName, false);
    }

    /// <summary>
    /// Retrieves the set of OdbcParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connection">A valid OdbcConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
    /// <returns>An array of OdbcParameters</returns>
    internal static OdbcParameter[] GetSpParameterSet(OdbcConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        using (OdbcConnection clonedConnection = (OdbcConnection)((ICloneable)connection).Clone())
        {
            return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
        }
    }

    /// <summary>
    /// Retrieves the set of OdbcParameters appropriate for the stored procedure
    /// </summary>
    /// <param name="connection">A valid OdbcConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
    /// <returns>An array of OdbcParameters</returns>
    private static OdbcParameter[] GetSpParameterSetInternal(OdbcConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

        OdbcParameter[] cachedParameters;

        cachedParameters = paramCache[hashKey] as OdbcParameter[];
        if (cachedParameters == null)
        {
            OdbcParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
            paramCache[hashKey] = spParameters;
            cachedParameters = spParameters;
        }

        return CloneParameters(cachedParameters);
    }

    #endregion Parameter Discovery Functions

}
}
